package cn.itcast.dao.impl;

import cn.itcast.dao.UserDao;
import cn.itcast.domain.User;
import cn.itcast.util.JDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class UserDaoImpl implements UserDao {

    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<User> findAll() {

        String sql = "select * from user";
        List<User> users = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return users;
    }

    @Override
    public User findUserByUsernameAndPassword(String username, String password) {
        try {
            String sql = "select * from user where username= ? and password = ?";
            User user = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class),
                    username, password);
            return user;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public void AddUser(User user) {
        //定义sql
        String sql = "insert into user values(null,?,?,?,?,?,?,null,null)";
        //执行sql
        template.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail());
    }

    @Override
    public void delUser(int id) {
        //定义sql
        String sql = "delete from user where id=?";
        //执行sql
        template.update(sql, id);
    }

    @Override
    public User findUser(int id) {
        //定义sql
        String sql = "select * from user where id = ?";
        return template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
    }

    @Override
    public void update(User user) {
        String sql = "update user set name = ?,gender = ? ,age = ? , address = ? , qq = ?, email = ? where id = ?";
        template.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail(), user.getId());
    }

    /**
     * 查询总记录数
     *
     * @param condition
     */
    @Override
    public int findTotalCount(Map<String, String[]> condition) {
        /*String sql = "select count(*) from user";
        return template.queryForObject(sql, Integer.class);*/

        //改造成分页条件查询
        //1.定义模板初始化sql
        String sql = "select count(*) from user where 1=1 ";
        StringBuilder sb = new StringBuilder(sql);
        //2.遍历map，根据map的value是否有值，来拼接sql
        Set<String> keySet = condition.keySet();//使用keySet遍历方式
        //定义参数的集合(用来加问号?条件的值)
        List<Object> params = new ArrayList<Object>();
        for (String key : keySet) {

            //排除分页条件参数
            if ("currentPage".equals(key) || "rows".equals(key)) {
                continue;
            }

            //获取value
            //map拿到的值是数组，不考虑数组的情况，直接[0]明确地获取一个值
            String value = condition.get(key)[0];
            //判断value是否有值
            if (value != null && !"".equals(value)) {
                //有值
                sb.append(" and " + key + " like ? ");//注意加空格的细节
                params.add("%" + value + "%");//拼了几个value就有几个,用来加问号?条件的值
            }
        }
        sql = sb.toString();
        //打印一下现在的sql
        System.out.println(sql);
        //打印一下参数
        System.out.println(params);

        //params.toArray()使用可变参，传个数组放这
        return template.queryForObject(sql, Integer.class, params.toArray());
    }

    /**
     * 分页查询每页记录
     */
    @Override
    public List<User> findByPage(int start, int rows, Map<String, String[]> condition) {
        List<User> list = null;
        try {
            String sql = "select * from user where 1=1 ";
            StringBuilder sb = new StringBuilder(sql);
            //2.遍历map，根据map的value是否有值，来拼接sql
            Set<String> keySet = condition.keySet();//使用keySet遍历方式
            //定义参数的集合(用来加问号?条件的值)
            List<Object> params = new ArrayList<Object>();
            for (String key : keySet) {

                //排除分页条件参数
                if ("currentPage".equals(key) || "rows".equals(key)) {
                    continue;
                }

                //获取value
                //map拿到的值是数组，不考虑数组的情况，直接[0]明确地获取一个值
                String value = condition.get(key)[0];
                //判断value是否有值
                if (value != null && !"".equals(value)) {
                    //有值
                    sb.append(" and " + key + " like ? ");//注意加空格的细节
                    params.add("%" + value + "%");//拼了几个value就有几个,用来加问号?条件的值
                }
            }
            //添加分页查询
            sb.append(" limit ?,? ");
            //添加分页查询参数值
            params.add(start);
            params.add(rows);
            sql = sb.toString();
            //打印一下现在的sql
            System.out.println(sql);
            //打印一下参数
            System.out.println(params);
            list = template.query(sql, new BeanPropertyRowMapper<User>(User.class), params.toArray());
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return list;
    }
}
